import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class PreparedStatementTest {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        SimpleDateFormat hmFromat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");// 格式化日期

        // String driverName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test";
        /*连接test数据库，userUnicode和characterEncoding是正常插入中文所需要的，不过mysql较新版本无需在url中指定相应参数*/
        //String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
        
        String userName = "root";
        String password = "kfc@wanda";
        String driverName = "com.mysql.jdbc.Driver";

        // 数据库包含很多表 test.students , test.teachers, test.courses
        try {
            
            Class.forName(driverName);//jdbc4.0后无需载入驱动，不过为了保证兼容性一般保留  
            con = DriverManager.getConnection(url, userName, password);

            // 根据参数的插入数据
            String strSql = "insert into students(stuno,name,gender,birthdate,major,age) values(?,?,?,?,?,?)";
            pStatement = con.prepareStatement(strSql);
            pStatement.setString(1, "20150111");
            pStatement.setString(2, "周迅");
            pStatement.setString(3, "f");
            pStatement.setString(4, hmFromat.format(new Date()));
            pStatement.setString(5, "电气工程");
            pStatement.setInt(6, 18);
            pStatement.executeUpdate();
            pStatement.close();// 立即释放资源

            // 根据参数删除数据
            strSql = "delete from students where id > ?";
            pStatement = con.prepareStatement(strSql);
            pStatement.setInt(1, 3);
            int row = pStatement.executeUpdate();
            System.out.println("成功删除了" + row + "行数据!");
            pStatement.close();// 立即释放资源

            // 根据参数更新数据
            strSql = "update students set major = ? where Id = ?";
            pStatement = con.prepareStatement(strSql);
            pStatement.setString(1, "新闻");
            pStatement.setInt(2, 5);
            row = pStatement.executeUpdate();
            System.out.println("成功更新了" + row + "行数据!");
            pStatement.close();// 立即释放资源

            // 根据参数查询数据
            strSql = "select * from students where Id < ?";
            pStatement = con.prepareStatement(strSql);
            pStatement.setInt(1, 10);
            rs = pStatement.executeQuery();
            // 数据库中存在如下记录
            strSql = "select * from students";
            rs = pStatement.executeQuery(strSql);

            System.out.println("id\t编号 \t\t姓名\t性别\t出生年月日\t\t专业");
            while (rs.next()) {

                // 通过列的下标(index)取数据
                System.out.print(rs.getInt(1) + "\t");
                System.out.print(rs.getString(2) + "        ");
                System.out.print(rs.getString(3) + "\t");
                System.out.print(rs.getString(4) + "\t ");
                System.out.print(rs.getDate(5) + "\t");// 这列有错,why?
                System.out.println(rs.getString(6));

                // 通过列名取数据(推荐大家都是用这种方式取数据)
                /*
                 * System.out.println(rs.getInt("id"));
                 * System.out.println(rs.getDate("birthdate"));
                 * System.out.println(rs.getInt("age"));
                 */

            }
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("没有找到驱动类!");
            e.printStackTrace();
        }
        finally {
            if (rs != null)
                try {
                    rs.close();
                    rs = null;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            if (pStatement != null)
                try {
                    pStatement.close();// 关闭语句
                    pStatement = null;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            if (con != null) {
                try {
                    con.close();// 关闭连接
                    con = null;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
